﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Collections.ObjectModel;
using System.Configuration;
using System.Reflection;
using System.Reflection.Emit;
using System.Runtime.Caching;

namespace Core.DataProvider
{
    public partial class SqlConnector : IDisposable
    {
        private static string _connectionString = string.Empty;
        private static ObjectCache memCache;
        private static CacheItemPolicy cachePol;

        private static SqlCommand BuildCommand(string query, List<SqlParameter> sqlParameters, string connectionString)
        {
            SqlCommand cmd = new SqlCommand(query, new SqlConnection(connectionString));
            if (sqlParameters != null)
            {
                foreach (SqlParameter p in sqlParameters)
                {
                    cmd.Parameters.Add(p);
                }
            }
            return cmd;
        }

        //private static SqlCommand BuildCommand(string query, List<Parameter> sqlParameters, string connectionString)
        //{
        //    SqlCommand cmd = new SqlCommand(query, new SqlConnection(connectionString));
        //    if (sqlParameters != null)
        //    {
        //        foreach (Parameter p in sqlParameters)
        //        {
        //            cmd.Parameters.AddWithValue(p.Name, p.Value);
        //        }
        //    }
        //    return cmd;
        //}

        private static DataTable FillDataTable(SqlCommand cmd)
        {
            DataTable result = new DataTable();
            result.Locale = System.Globalization.CultureInfo.CurrentCulture;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            try
            {
                cmd.Connection.Open();
                adapter.Fill(result);
            }
            catch (Exception ex)
            {
                Logging.LogError(ex.ToString());
                throw;
            }
            finally
            {
                //Fix for bug : http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/80ed858a-64e3-4821-a1a3-44940fbf4e83
                cmd.Parameters.Clear(); 

                cmd.Connection.Close();
            }
            return result;
        }

        private static string ExecScalarCmd(SqlCommand cmd)
        {
            string r = string.Empty;
            try
            {
                cmd.Connection.Open();
                r = cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                Logging.LogError(ex.ToString());
                throw;
            }
            finally
            {
                //Fix for bug : http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/80ed858a-64e3-4821-a1a3-44940fbf4e83
                cmd.Parameters.Clear();

                cmd.Connection.Close();
            }
            return r;
        }

        private static int ExecNonQuery(SqlCommand cmd)
        {
            int result = -1;
            try
            {
                cmd.Connection.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logging.LogError(ex.ToString());
                throw;
            }
            finally
            {
                //Fix for bug : http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/80ed858a-64e3-4821-a1a3-44940fbf4e83
                cmd.Parameters.Clear();

                cmd.Connection.Close();
            }
            return result;
        }

        public SqlConnector()
        {
            Init(ConfigurationManager.ConnectionStrings["SqlMonitoringDB"].ConnectionString);
        }

        public SqlConnector(string connectionString)
        {
            Init(connectionString);
        }

        private void Init(string connectionString)
        {
            _connectionString = connectionString;
            if(memCache == null) memCache = MemoryCache.Default;
            CacheItemPolicy cachePol = new CacheItemPolicy();
            cachePol.AbsoluteExpiration = DateTimeOffset.Now.AddDays(1);
            InitMainConnector();
            InitDataConnector();
        }

        private void InitMainConnector()
        {

        }

        public string ConnectionString { get { return _connectionString; } set { _connectionString = value; } }

        //public List<DataRow> ExecuteReader(string query, List<Parameter> sqlParameters = null, string connectionString = null)
        //{
        //    connectionString = CheckConnectionString(connectionString);
        //    SqlCommand cmd = BuildCommand(query, sqlParameters, connectionString);            
        //    cmd.CommandType = CommandType.StoredProcedure;
            
        //    DataTable result = new DataTable();
        //    cmd.Connection.Open();
        //    result.Load(cmd.ExecuteReader());
        //    cmd.Connection.Close();
        //    return result.AsEnumerable().ToList();
        //}

        public string ExecuteScalar(string storedProcedure, List<SqlParameter> sqlParameters = null, string connectionString = null)
        {
            connectionString = CheckConnectionString(connectionString);
            SqlCommand cmd = BuildCommand(storedProcedure, sqlParameters, connectionString);
            cmd.CommandType = CommandType.StoredProcedure;
            return ExecScalarCmd(cmd);
        }

        //public string ExecuteScalar(string storedProcedure, List<Parameter> sqlParameters = null, string connectionString = null)
        //{
        //    connectionString = CheckConnectionString(connectionString);
        //    SqlCommand cmd = BuildCommand(storedProcedure, sqlParameters, connectionString);
        //    cmd.CommandType = CommandType.StoredProcedure;
        //    return ExecScalarCmd(cmd);
        //}

        public string ExecuteScalarText(string query, List<SqlParameter> sqlParameters = null, string connectionString = null)
        {
            connectionString = CheckConnectionString(connectionString);
            SqlCommand cmd = BuildCommand(query, sqlParameters, connectionString);
            cmd.CommandType = CommandType.Text;
            return ExecScalarCmd(cmd);
        }
        
        //public string ExecuteScalarText(string query, List<Parameter> sqlParameters = null, string connectionString = null)
        //{
        //    connectionString = CheckConnectionString(connectionString);
        //    SqlCommand cmd = BuildCommand(query, sqlParameters, connectionString);
        //    cmd.CommandType = CommandType.Text;
        //    return ExecScalarCmd(cmd);
        //}

        //public int ExecuteNonQuery(string query, List<Parameter> sqlParameters = null, string connectionString = null)
        //{
        //    connectionString = CheckConnectionString(connectionString);
        //    SqlCommand cmd = BuildCommand(query, sqlParameters, connectionString);
        //    cmd.CommandType = CommandType.StoredProcedure;
        //    return ExecNonQuery(cmd);
        //}

        public int ExecuteNonQuery(string query, List<SqlParameter> sqlParameters = null, string connectionString = null)
        {
            connectionString = CheckConnectionString(connectionString);
            SqlCommand cmd = BuildCommand(query, sqlParameters, connectionString);
            cmd.CommandType = CommandType.StoredProcedure;
            return ExecNonQuery(cmd);
        }

        public List<DataRow> ExecuteStoredProcedure(string storedProcedure, List<SqlParameter> sqlParameters = null, string connectionString = null)
        {
            connectionString = CheckConnectionString(connectionString);
            SqlCommand cmd = BuildCommand(storedProcedure, sqlParameters, connectionString);
            cmd.CommandType = CommandType.StoredProcedure;
            return FillDataTable(cmd).AsEnumerable().ToList();
        }

        public List<DataRow> ExecuteSqlText(string query, List<SqlParameter> sqlParameters = null, string connectionString = null)
        {
            connectionString = CheckConnectionString(connectionString);
            SqlCommand cmd = BuildCommand(query, sqlParameters, connectionString);
            cmd.CommandType = CommandType.Text;
            return FillDataTable(cmd).AsEnumerable().ToList();
        }

        //To use: make sure the data types match and the colomn names of the query match the class ( inc casing )
        public static List<T> Reader<T>(string storedProcedure, List<SqlParameter> sqlParameters = null, string connectionString = null)
        {
            connectionString = CheckConnectionString(connectionString);

            SqlCommand cmd = BuildCommand(storedProcedure, sqlParameters, connectionString);
            cmd.CommandType = CommandType.StoredProcedure;

            string TType = typeof(T).ToString();
            bool TinCache = memCache.Contains(TType);

            List<T> result = new List<T>();

            try
            {
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                //When T in Cache, then use the cached builer, otherwise build the builder, store it, and use it.
                DynamicBuilder<T> builder;
                if (TinCache)
                {
                    builder = (DynamicBuilder<T>)memCache[TType];
                }
                else
                {
                    builder = DynamicBuilder<T>.CreateBuilder(reader);
                    memCache.Add(new CacheItem(TType, (object)builder), cachePol);
                }
                while (reader.Read())
                {
                    result.Add(builder.Build(reader));
                }
            }
            catch (Exception ex)
            {
                Logging.LogError(ex.ToString());
                throw;
            }
            finally
            {
                //Fix for bug : http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/80ed858a-64e3-4821-a1a3-44940fbf4e83
                cmd.Parameters.Clear();

                cmd.Connection.Close();
            }
            return result;
        }

        private static string CheckConnectionString(string connectionString)
        {
            if ((connectionString == null)||(connectionString == string.Empty))
            {
                if (_connectionString == string.Empty) _connectionString = ConfigurationManager.ConnectionStrings["SqlMonitoringDB"].ConnectionString;
                connectionString = _connectionString;
            }
            return connectionString;
        }

        #region IDisposable Members

        public void Dispose()
        {
        }

        #endregion
    }

    //public struct Parameter
    //{
    //    public string Name;
    //    public object Value;
    //}
}
